﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace AirplaneReservation
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack == false)
            {
                one.Checked = true;
            }
        }

        protected void one_CheckedChanged(object sender, EventArgs e)
        {
            //round.Checked = false;
            Panel1.Visible = true;
            Panel2.Visible = false;
        }

        protected void round_CheckedChanged(object sender, EventArgs e)
        {
            Panel1.Visible = false;
            Panel2.Visible = true;
        }

        protected void search_Click(object sender, EventArgs e)
        {
            string origin = From.SelectedItem.ToString();
            string des = to.SelectedItem.ToString();
            string class_n = drlClass.SelectedItem.ToString();
            int a = int.Parse(adult.Text);
            int c = int.Parse(children.Text);
            int s = int.Parse(senior.Text);
            int total = a + c + s;
            TextBox lb = (TextBox)departDate.FindControl("txtDate");
            string departD = lb.Text;

            SqlConnection ccon = new SqlConnection(ConfigurationManager.ConnectionStrings["airplaneConnectionString"].ToString());
            ccon.Open();

            string sql = "select FlightCode, originCity, desCity, DepartureTime as Departure, Class_Name as Class, AvalaibleSeats as Avail, Flight_Id, ClassId, Duration from View_Map where originCity='" + origin + "' and desCity='" + des + "' and Class_Name='" + class_n + "' and  AvalaibleSeats>=" + total + " and Convert(varchar(10),DepartureTime,101)='" + departD + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, ccon);
            DataTable dt = new DataTable();
            da.Fill(dt);

            int count = dt.Rows.Count;
            if (count != 0)
            {
                GridView1.DataSource = dt;
                GridView1.DataBind();
                GridView1.Visible = true;
                Label4.Text = "";
            }
            if (count == 0)
            {
                dt.Dispose();
                da.Dispose();

                string sql2 = "select FlightCode, originCity, DesCity, DepartureTime as Departure, Class_Name as Class, AvalaibleSeats as Avail, FlightId, ClassId from View_Router where originCity='" + origin + "' and DesCity='" + des + "' and Class_Name='" + class_n + "' and  AvalaibleSeats>=" + total + " and Convert(varchar(10),DepartureTime,101)='" + departD + "'";
                SqlDataAdapter da2 = new SqlDataAdapter(sql2, ccon);
                DataTable dt2 = new DataTable();
                da2.Fill(dt2);

                int count2 = dt2.Rows.Count;
                if (count2 != 0)
                {
                    GridView1.DataSource = dt2;
                    GridView1.DataBind();

                    GridView1.Visible = true;
                    Label4.Text = "";
                }
                else
                {
                    Label4.Text = "There is no flight match to the conditions";
                    GridView1.Visible = false;
                    Panel3.Visible = false;
                    Label5.Text = "";
                }
            }
            ccon.Close();
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            int a = int.Parse(adult.Text);
            int c = int.Parse(children.Text);
            int s = int.Parse(senior.Text);
            int total = a + c + s;
            Panel3.Visible = true;

            SqlConnection ccon = new SqlConnection(ConfigurationManager.ConnectionStrings["airplaneConnectionString"].ToString());
            ccon.Open();

            string sql3 = "select (StandardPrice*PercentPrice/100) as Price from View_Map where FlightCode='" + GridView1.SelectedRow.Cells[1].Text + "'";
            SqlDataAdapter da3 = new SqlDataAdapter(sql3, ccon);
            DataTable dt3 = new DataTable();
            da3.Fill(dt3);

            Double p = Convert.ToDouble(dt3.Rows[0]["Price"].ToString());
            Double t = total * p;
            toTal.Text = t.ToString();
            TextBox1.Text = "If your flight departure is within the next 48 hours please call the respective airline, cancel the reservation and collect a cancellation reference number. Mail these details to us by filling the cancellation form given below.\nIf your flight departure is over 48 hours you may send us the cancellation details by filling the form below. We will arrange to cancel the ticket for you with the airline and process the refund.\n If your flight is cancelled /delayed , we request you to get the cancellation stamp from the respective Airline counter on the ticket and mail the same to us at the below mentioned address or you can scan the cancelled ticket.";
        }

        protected void block_Click(object sender, EventArgs e)
        {
            int a = int.Parse(adult.Text);
            int c = int.Parse(children.Text);
            int s = int.Parse(senior.Text);
            int total = a + c + s;

            string memberId = Session["ssId"].ToString();
            string cusId = Session["ssCustomerID"].ToString();
            string flightId = GridView1.SelectedRow.Cells[7].Text;
            string classId = GridView1.SelectedRow.Cells[8].Text;
            string codeB = "Blocking" + flightId + classId + memberId + total.ToString();
            int avail = int.Parse(GridView1.SelectedRow.Cells[6].Text);
            int availAfter = avail - total;
            int sta = 0;

            string dt_Now = DateTime.Now.ToString("MM/dd/yyyy");
            TextBox lb = (TextBox)departDate.FindControl("txtDate");
            string departD = lb.Text;
            TimeSpan timespan = Convert.ToDateTime(departD).Subtract(Convert.ToDateTime(dt_Now));
            int dayBetween = int.Parse(timespan.TotalDays.ToString());

            if (dayBetween > 14)
            {
                SqlConnection ccon = new SqlConnection(ConfigurationManager.ConnectionStrings["airplaneConnectionString"].ToString());
                ccon.Open();

                string sql = "insert into Ticket(MemberId, CustomerId, FlightId, ClassId, Code, Ticket.Status, FinalPrice) values (" + memberId + ", " + cusId + ", " + flightId + ", " + classId + ", '" + codeB + "', " + sta + ", " + toTal.Text + ") ";
                SqlCommand cmd = new SqlCommand(sql, ccon);
                cmd.ExecuteNonQuery();

                string sql2 = "update View_AvalaibleSeats set AvalaibleSeats=" + availAfter + " where FlightId=" + flightId + " and ClassId=" + classId + "";
                SqlCommand cmd2 = new SqlCommand(sql2, ccon);
                cmd2.ExecuteNonQuery();
                Label5.Text = codeB;

                ccon.Close();
            }
            else showMessage("You can only buy!!!");
        }

        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Cells[7].Visible = false;
                e.Row.Cells[8].Visible = false;
            }
            if (e.Row.RowType == DataControlRowType.Header)
            {
                e.Row.Cells[7].Visible = false;
                e.Row.Cells[8].Visible = false;
            }
        }

        public void showMessage(string mess)
        {
            string strBuilder = "<script language='javascript'>alert('" + mess + "')</script>";
            Response.Write(strBuilder);
        }

        protected void buy_Click(object sender, EventArgs e)
        {
            int a = int.Parse(adult.Text);
            int c = int.Parse(children.Text);
            int s = int.Parse(senior.Text);
            int total = a + c + s;

            string memberId = Session["ssId"].ToString();
            string cusId = Session["ssCustomerID"].ToString();
            string flightId = GridView1.SelectedRow.Cells[7].Text;
            string classId = GridView1.SelectedRow.Cells[8].Text;

            string codeB = "Confirming" + flightId + classId + memberId + total.ToString();
            int avail = int.Parse(GridView1.SelectedRow.Cells[6].Text);
            int availAfter = avail - total;
            int sta = 1;

            string dt_Now = DateTime.Now.ToString("MM/dd/yyyy");
            TextBox lb = (TextBox)departDate.FindControl("txtDate");
            string departD = lb.Text;
            TimeSpan timespan = Convert.ToDateTime(departD).Subtract(Convert.ToDateTime(dt_Now));
            int dayBetween = int.Parse(timespan.TotalDays.ToString());

            if (dayBetween >= 1)
            {
                SqlConnection ccon = new SqlConnection(ConfigurationManager.ConnectionStrings["airplaneConnectionString"].ToString());
                ccon.Open();

                string sql3 = "select Money from CreditCard where Code='"+Session["ssCredit"].ToString()+"'";
                SqlDataAdapter da = new SqlDataAdapter(sql3, ccon);
                DataTable at = new DataTable();
                da.Fill(at);
                Double money = Convert.ToDouble(at.Rows[0]["Money"].ToString());
                Double amount = Convert.ToDouble(toTal.Text);
                Double moneyAfter = money - amount;

                if(money>=amount)
                {
                    string sql = "insert into Ticket(MemberId, CustomerId, FlightId, ClassId, Code, Ticket.Status, FinalPrice) values (" + memberId + ", " + cusId + ", " + flightId + ", " + classId + ", '" + codeB + "', " + sta + ", " + toTal.Text + ") ";
                    SqlCommand cmd = new SqlCommand(sql, ccon);
                    cmd.ExecuteNonQuery();

                    string sql2 = "update View_AvalaibleSeats set AvalaibleSeats=" + availAfter + " where FlightId=" + flightId + " and ClassId=" + classId + "";
                    SqlCommand cmd2 = new SqlCommand(sql2, ccon);
                    cmd2.ExecuteNonQuery();

                    string sql4 = "update CreditCard set Money=" + moneyAfter + " where Code=" + Session["ssCredit"].ToString() + "";
                    SqlCommand cmd3 = new SqlCommand(sql4, ccon);
                    cmd3.ExecuteNonQuery();

                    string sql5 = "update CreditCard set Money=" + moneyAfter + " where Code=" + Session["ssCredit"].ToString() + "";
                    SqlCommand cmd4 = new SqlCommand(sql4, ccon);
                    cmd4.ExecuteNonQuery();

                    Label5.Text = codeB;
                }
                else showMessage("You dont have enough money to buy!!!");
                
                ccon.Close();
            }
                else showMessage("This is old flight. You can't buy or block!!!");
        }
    }
}